DROP TABLE code_file_handle IF EXISTS;
DROP TABLE enterprise_domain IF EXISTS;
DROP TABLE lookup_table IF EXISTS;
DROP TABLE qrcode_domain IF EXISTS;
DROP TABLE range_activation IF EXISTS;
DROP TABLE roll_range_info IF EXISTS;
DROP TABLE tag_batch IF EXISTS;
DROP TABLE tag_batch_config IF EXISTS;
DROP TABLE tag_operation_log IF EXISTS;

CREATE TABLE code_file_handle (
	id bigint IDENTITY(1,1) NOT NULL,
	create_date datetime2(7) NULL,
	create_user_id bigint NULL,
	update_date datetime2(7) NULL,
	update_user_id bigint NULL,
	container_name varchar(255) COLLATE Chinese_PRC_CI_AI NOT NULL,
	file_path varchar(255) COLLATE Chinese_PRC_CI_AI NOT NULL,
	file_type int NOT NULL,
	ese_id bigint NOT NULL,
	parse_pass_word varchar(255) COLLATE Chinese_PRC_CI_AI NOT NULL,
	PRIMARY KEY CLUSTERED (id)
);

CREATE NONCLUSTERED INDEX index_file_path
ON code_file_handle (file_path ASC)
WITH (IGNORE_DUP_KEY = OFF,
	STATISTICS_NORECOMPUTE = OFF,
	ONLINE = OFF);

CREATE TABLE enterprise_domain (
	id bigint NOT NULL,
	ese_id bigint NULL DEFAULT ((0)),
	domain_id bigint NULL DEFAULT ((0)),
	sub_domain varchar(2) COLLATE Chinese_PRC_CI_AI NOT NULL DEFAULT '',
	status int NULL DEFAULT ((0)),
	deleted bit NULL DEFAULT ((0)),
	create_user_id bigint NULL DEFAULT ((0)),
	create_date datetime2(7) NULL DEFAULT (getdate()),
	update_user_id bigint NULL DEFAULT ((0)),
	update_date datetime2(7) NULL DEFAULT (getdate()),
	PRIMARY KEY CLUSTERED (id) 
	WITH (IGNORE_DUP_KEY = OFF)
);

CREATE TABLE lookup_table (
	id bigint IDENTITY(1,1) NOT NULL,
	seqnum_start bigint NOT NULL,
	seqnum_end bigint NOT NULL,
	current_count bigint NOT NULL,
	version int NULL,
	create_user_id bigint NULL,
	create_date datetime2(7) NULL DEFAULT (getdate()),
	update_user_id bigint NULL,
	update_date datetime2(7) NULL,
	PRIMARY KEY CLUSTERED (id) 
	WITH (IGNORE_DUP_KEY = OFF)
)
GO

CREATE TABLE qrcode_domain (
	id bigint IDENTITY(1,1) NOT NULL,
	domain_url nvarchar(255) COLLATE Chinese_PRC_CI_AI NULL DEFAULT '',
	create_user_id bigint NULL DEFAULT ((0)),
	create_date datetime2(7) NULL DEFAULT (getdate()),
	update_user_id bigint NULL DEFAULT ((0)),
	update_date datetime2(7) NULL DEFAULT '',
	status int NULL DEFAULT ((0)),
	PRIMARY KEY CLUSTERED (id) 
	WITH (IGNORE_DUP_KEY = OFF)
);

CREATE TABLE range_activation (
	id bigint IDENTITY(1,1) NOT NULL,
	ese_id bigint NOT NULL DEFAULT ((0)),
	seqnum_start bigint NOT NULL DEFAULT ((0)),
	seqnum_end bigint NOT NULL DEFAULT ((0)),
	product_id bigint NOT NULL DEFAULT ((0)),
	factory_id bigint NOT NULL DEFAULT ((0)),
	domain varchar(50) COLLATE Chinese_PRC_CI_AI NULL DEFAULT '',
	sub_domain varchar(5) COLLATE Chinese_PRC_CI_AI NULL DEFAULT '',
	franchiser_id bigint NULL DEFAULT ((0)),
	tag_batch_id bigint NULL DEFAULT ((0)),
	activation_method int NULL DEFAULT ((0)),
	activation_param varchar(255) COLLATE Chinese_PRC_CI_AI NULL DEFAULT '',
	create_user_id bigint NULL DEFAULT ((0)),
	create_date datetime2(0) NULL DEFAULT (getdate()),
	update_user_id bigint NULL DEFAULT ((0)),
	update_date datetime2(0) NULL DEFAULT (getdate()),
	is_all bit NOT NULL DEFAULT ((0)),
	status int NOT NULL DEFAULT ((0)),
	PRIMARY KEY CLUSTERED (id) 
	WITH (IGNORE_DUP_KEY = OFF)
);

CREATE TABLE roll_range_info (
	id bigint IDENTITY(1,1) NOT NULL,
	tag_batch_id bigint NOT NULL,
	seqnum_start bigint NOT NULL,
	seqnum_end bigint NOT NULL,
	ese_id bigint NULL,
	sub_domain varchar(5) COLLATE Chinese_PRC_CI_AI NULL,
	status int NULL,
	PRIMARY KEY CLUSTERED (id) 
	WITH (IGNORE_DUP_KEY = OFF)
);


CREATE TABLE tag_batch (
	id bigint IDENTITY(1,1) NOT NULL,
	name nvarchar(255) COLLATE Chinese_PRC_CI_AI NULL DEFAULT '',
	tag_count bigint NOT NULL DEFAULT ((0)),
	type int NULL DEFAULT ((1)),
	status int NULL DEFAULT ((0)),
	lookup_id bigint NOT NULL DEFAULT ((0)),
	seqnum_start bigint NOT NULL DEFAULT ((0)),
	seqnum_end bigint NOT NULL DEFAULT ((0)),
	ese_id bigint NOT NULL DEFAULT ((0)),
	master_ese_id bigint NOT NULL DEFAULT ((0)),
	tag_batch_config_id bigint NOT NULL DEFAULT ((0)),
	domain varchar(50) COLLATE Chinese_PRC_CI_AI NULL DEFAULT '',
	sub_domain varchar(5) COLLATE Chinese_PRC_CI_AI NULL DEFAULT ('Q'),
	unit_count int NULL DEFAULT ((0)),
	unit_tag_count bigint NOT NULL DEFAULT ((0)),
	redundancy float(53) NULL DEFAULT ((0)),
	ratio int NULL DEFAULT ((0)),
	duplicate int NULL DEFAULT ((0)),
	source varchar(50) COLLATE Chinese_PRC_CI_AI NULL DEFAULT '',
	print_file_status int NOT NULL DEFAULT ((0)),
	zip_password varchar(100) COLLATE Chinese_PRC_CI_AI NULL DEFAULT '',
	create_date datetime2(7) NULL DEFAULT (getdate()),
	update_date datetime2(7) NULL DEFAULT (getdate()),
	create_user_id bigint NULL DEFAULT ((0)),
	update_user_id bigint NULL DEFAULT ((0)),
	version int NULL DEFAULT ((0)),
	code_length int NOT NULL DEFAULT ((0)),
	PRIMARY KEY CLUSTERED (id) 
	WITH (IGNORE_DUP_KEY = OFF)
);

CREATE TABLE tag_batch_config (
	id bigint IDENTITY(1,1) NOT NULL,
	name varchar(255) COLLATE Chinese_PRC_CI_AI NULL DEFAULT '',
	print_factory_id bigint NULL DEFAULT ((0)),
	terminal_id bigint NULL DEFAULT ((0)),
	tag_count_per_line int NULL DEFAULT ((0)),
	url_domain varchar(100) COLLATE Chinese_PRC_CI_AI NULL DEFAULT '',
	direction int NULL DEFAULT ((0)),
	fields_config varchar(500) COLLATE Chinese_PRC_CI_AI NULL DEFAULT '',
	file_type varchar(255) COLLATE Chinese_PRC_CI_AI NULL DEFAULT '',
	download_method int NULL DEFAULT ((0)),
	tag_batch_id bigint NULL DEFAULT ((0)),
	create_date datetime2(0) NULL DEFAULT (getdate()),
	create_user_id bigint NULL DEFAULT ((0)),
	update_date datetime2(0) NULL DEFAULT (getdate()),
	update_user_id bigint NULL DEFAULT ((0)),
	PRIMARY KEY CLUSTERED (id) 
	WITH (IGNORE_DUP_KEY = OFF)
)
GO

CREATE TABLE tag_operation_log (
	id bigint IDENTITY(1,1) NOT NULL,
	operation varchar(255) COLLATE Chinese_PRC_CI_AI NULL DEFAULT '',
	range varchar(255) COLLATE Chinese_PRC_CI_AI NULL DEFAULT '',
	source varchar(255) COLLATE Chinese_PRC_CI_AI NULL DEFAULT '',
	user_name varchar(255) COLLATE Chinese_PRC_CI_AI NULL DEFAULT '',
	tag_batch_id bigint NOT NULL DEFAULT ((0)),
	range_id bigint NULL DEFAULT ((0)),
	create_date datetime2(7) NULL DEFAULT (getdate()),
	create_user_id bigint NULL DEFAULT ((0)),
	update_date datetime2(7) NULL DEFAULT (getdate()),
	update_user_id bigint NULL DEFAULT ((0)),
	PRIMARY KEY CLUSTERED (id) 
	WITH (IGNORE_DUP_KEY = OFF)
);

ALTER TABLE roll_range_info ADD FOREIGN KEY (tag_batch_id) REFERENCES tag_batch (id) ON DELETE NO ACTION ON UPDATE NO ACTION;
